package com.stylefeng.guns.core.excel;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil {

	public enum ExcelVision{
		XLS,//2003
		XLSX,//2007
		NONE;
	}
	
	public static ExcelVision getVision(String fileName){
		if(fileName.endsWith(ExcelVision.XLS.name().toLowerCase()))
			return ExcelVision.XLS;
		else if(fileName.endsWith(ExcelVision.XLSX.name().toLowerCase()))
			return ExcelVision.XLSX;
		else
			return ExcelVision.NONE;
	}
	
	/**
	 * 读取excel
	 * @param input excel流
	 * @param startRowIndex 开始行索引
	 * @param mappings 列与属性映射
	 * @param clazz 结果bean class
	 * @return 结果
	 * @throws Exception
	 */
	public static <T> List<T> readExcel(InputStream input,ExcelVision vision, int startRowIndex, ExcleMappings mappings, Class<T> clazz) throws Exception{
		Workbook workBook = null;
		if(vision.equals(ExcelVision.XLS)){
			workBook = new HSSFWorkbook( input );
		}else if(vision.equals( ExcelVision.XLSX)){
			workBook = new XSSFWorkbook(input);
		}else{
			throw new RuntimeException("Not supported Excel Vision");
		}
		return readWorkBook(workBook, startRowIndex, mappings, clazz);
	}
	
	public static <T> List<T> readWorkBook(Workbook workBook,int startRowIndex,ExcleMappings mappings, Class<T> clazz)throws Exception{
		List<T> result = new ArrayList<T>();
		// 循环工作表Sheet
        for (int numSheet = 0; numSheet < workBook.getNumberOfSheets(); numSheet++) {
            Sheet sheet = workBook.getSheetAt( numSheet );
            if (sheet == null) {
                continue;
            }
            // 循环行Row
            for (int rowNum = startRowIndex; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                T t = mappingRow(mappings,row,clazz);
                if (t != null) {
                	result.add( t );
                }
            }
        }
		return result;
	}
	
	/**
	 * 转换excel行为对象
	 * @param mappings
	 * @param row
	 * @param clazz
	 * @return Object<T> OR null(存在非空列为空,row == null)
	 * @throws Exception
	 */
	private static <T> T mappingRow(ExcleMappings mappings, Row row, Class<T> clazz)throws Exception{
		if(null ==  row) return null;
		T target = clazz.newInstance();
		for(String propertyName : mappings.getMappings().keySet()){
			ExcelMapping mapping = mappings.getMappings().get(propertyName);
			if(null != mapping){
				Cell cell = row.getCell( mapping.getCloumnIndex() );
				Class<?> propertyClazz = mapping.getClazz();
				Object value = getCellValue(cell,propertyClazz);
				if(!mapping.isNullable() && (null == value || "".equals(value))){
					//存在空的不能为空列
					target = null;
					break;
				}
				//Object value = ReflectionUtil.convertStringToObject(strValue, propertyClazz);
				if(null != mapping.getConvert() && null != value){
					value = mapping.getConvert().convertValue(cell,value);
				}
				if(null != value)
					ReflectionUtil.invokeSetterMethod(target, propertyName, value);
			}
		}
		return target;
	}
	
	
    public static <T> Object convertType(String value,Class<T> clazz){
    	if(clazz == java.lang.String.class ){
    		return value;
    	}else if(clazz == java.util.Date.class ){
    		return DateUtil.getYYYYMMDDHHMMSS(value);
    	}else if(clazz == java.lang.Integer.class ){
    		return Integer.valueOf(value);
    	}else if(clazz == int.class){
    	    return Integer.parseInt(value);
    	}else if(clazz == java.lang.Double.class){
    		return Double.valueOf(value);
    	}else if(clazz == double.class){
    		return Double.parseDouble(value);
    	}else if( clazz == java.lang.Long.class){
    		return Long.valueOf(value);
    	}else if(clazz == long.class){
    		return Long.parseLong(value);
    	}else if(clazz==float.class){
    		return Float.parseFloat(value);
    	}else{
    		return null;
    	}
    }
    
    public static <T> Object getCellValue(Cell cell,Class<T> clazz){
    	try {
    		if(null == cell)
        		return null;
        	if(clazz == java.lang.String.class ){
        		return getCellStrValue(cell);
        	}else if(clazz == java.util.Date.class ){
        		return cell.getDateCellValue();
        	}else if(clazz == java.lang.Integer.class ){
        		return Integer.valueOf(getCellStrValue(cell));
        	}else if(clazz == int.class){
        	    return Integer.parseInt(getCellStrValue(cell));
        	}else if(clazz == java.lang.Double.class){
        		return Double.valueOf(cell.getNumericCellValue());
        	}else if(clazz == double.class){
        		return getCellStrValue(cell);
//        		return cell.getNumericCellValue();
        	}else if( clazz == java.lang.Long.class){
//        		return Long.valueOf(getCellStrValue(cell));
        		return getCellStrValue(cell);
        	}else if(clazz == long.class){
//        		return Long.parseLong(getCellStrValue(cell));
        		return getCellStrValue(cell);
        	}else{
        		return null;
        	}
		} catch (Exception e) {
			return null;
			//e.printStackTrace();
		}
    }
   
    
	/**
     * 得到Excel表中的值
     * 
     * @param hssfCell
     *            Excel中的每一个格子
     * @return Excel中每一个格子中的值
     */
    public static String getCellStrValue(Cell cell) {
    	String value = "";
    	if(null != cell){
    		switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC: // 数值型
                 if (HSSFDateUtil.isCellDateFormatted(cell)) {
    	             //如果是date类型则 ，获取该cell的date值
                	 Date date = cell.getDateCellValue();
    	             value = DateUtil.format(date, "yyyy-MM-dd hh:mm:ss");
                 }else{//纯数字
    	             value = String.valueOf(cell.getNumericCellValue());
                 }
                break;
            /* 此行表示单元格的内容为string类型 */
            case HSSFCell.CELL_TYPE_STRING: // 字符串型
                value = cell.getRichStringCellValue().toString();
                break;
            case HSSFCell.CELL_TYPE_FORMULA://公式型
                //读公式计算值
                 value = String.valueOf(cell.getNumericCellValue());
                 if(value.equals("NaN")){//如果获取的数据值为非法值,则转换为获取字符串
                     value = cell.getRichStringCellValue().toString();
                 }
            break;
            case HSSFCell.CELL_TYPE_BOOLEAN://布尔
                 value = " "+ cell.getBooleanCellValue();
             break;
            /* 此行表示该单元格值为空 */
            case HSSFCell.CELL_TYPE_BLANK: // 空值
                value = "";
                break;
            case HSSFCell.CELL_TYPE_ERROR: // 故障
                value = "";
                break;
            default:
                value = cell.getRichStringCellValue().toString();
            }
    	}
    	return value;
    }
}
